﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Catering_DBOpertion;
using Catering_Model;//Please add references
namespace Catering_Operation
{
    /// <summary>
    /// 数据访问类:RecipeInformation
    /// </summary>
    public partial class D_RecipeInformation
    {
        public D_RecipeInformation()
        { }

        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from RecipeInformation");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(E_RecipeInformation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into RecipeInformation(");
            strSql.Append("FMID,Date,CID,MID,Week,Back,AVGWeight,Weight,Price,temp1,temp2,IsExamine,ExamineUserID,ExamineUserName)");
            strSql.Append(" values (");
            strSql.Append("@FMID,@Date,@CID,@MID,@Week,@Back,@AVGWeight,@Weight,@Price,@temp1,@temp2,@IsExamine,@ExamineUserID,@ExamineUserName)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@FMID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime),
					new SqlParameter("@CID", SqlDbType.Int,4),
					new SqlParameter("@MID", SqlDbType.Int,4),
					new SqlParameter("@Week", SqlDbType.Int,4),
					new SqlParameter("@Back", SqlDbType.VarChar,200),
					new SqlParameter("@AVGWeight", SqlDbType.VarChar,50),
					new SqlParameter("@Weight", SqlDbType.VarChar,50),
					new SqlParameter("@Price", SqlDbType.Float,8),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200),
					new SqlParameter("@IsExamine", SqlDbType.Int,4),
					new SqlParameter("@ExamineUserID", SqlDbType.Int,4),
					new SqlParameter("@ExamineUserName", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.FMID;
            parameters[1].Value = model.Date;
            parameters[2].Value = model.CID;
            parameters[3].Value = model.MID;
            parameters[4].Value = model.Week;
            parameters[5].Value = model.Back;
            parameters[6].Value = model.AVGWeight;
            parameters[7].Value = model.Weight;
            parameters[8].Value = model.Price;
            parameters[9].Value = model.temp1;
            parameters[10].Value = model.temp2;
            parameters[11].Value = model.IsExamine;
            parameters[12].Value = model.ExamineUserID;
            parameters[13].Value = model.ExamineUserName;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                model.id = Convert.ToInt32(obj);
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(E_RecipeInformation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update RecipeInformation set ");
            strSql.Append("FMID=@FMID,");
            strSql.Append("Date=@Date,");
            strSql.Append("CID=@CID,");
            strSql.Append("MID=@MID,");
            strSql.Append("Week=@Week,");
            strSql.Append("Back=@Back,");
            strSql.Append("AVGWeight=@AVGWeight,");
            strSql.Append("Weight=@Weight,");
            strSql.Append("Price=@Price,");
            strSql.Append("temp1=@temp1,");
            strSql.Append("temp2=@temp2,");
            strSql.Append("IsExamine=@IsExamine,");
            strSql.Append("ExamineUserID=@ExamineUserID,");
            strSql.Append("ExamineUserName=@ExamineUserName");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@FMID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime),
					new SqlParameter("@CID", SqlDbType.Int,4),
					new SqlParameter("@MID", SqlDbType.Int,4),
					new SqlParameter("@Week", SqlDbType.Int,4),
					new SqlParameter("@Back", SqlDbType.VarChar,200),
					new SqlParameter("@AVGWeight", SqlDbType.VarChar,50),
					new SqlParameter("@Weight", SqlDbType.VarChar,50),
					new SqlParameter("@Price", SqlDbType.Float,8),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200),
					new SqlParameter("@IsExamine", SqlDbType.Int,4),
					new SqlParameter("@ExamineUserID", SqlDbType.Int,4),
					new SqlParameter("@ExamineUserName", SqlDbType.NVarChar,50),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.FMID;
            parameters[1].Value = model.Date;
            parameters[2].Value = model.CID;
            parameters[3].Value = model.MID;
            parameters[4].Value = model.Week;
            parameters[5].Value = model.Back;
            parameters[6].Value = model.AVGWeight;
            parameters[7].Value = model.Weight;
            parameters[8].Value = model.Price;
            parameters[9].Value = model.temp1;
            parameters[10].Value = model.temp2;
            parameters[11].Value = model.IsExamine;
            parameters[12].Value = model.ExamineUserID;
            parameters[13].Value = model.ExamineUserName;
            parameters[14].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from RecipeInformation ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from RecipeInformation ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public E_RecipeInformation GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,FMID,Date,CID,MID,Week,Back,AVGWeight,Weight,Price,temp1,temp2,IsExamine,ExamineUserID,ExamineUserName from RecipeInformation ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
            parameters[0].Value = id;

            E_RecipeInformation model = new E_RecipeInformation();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FMID"].ToString() != "")
                {
                    model.FMID = int.Parse(ds.Tables[0].Rows[0]["FMID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Date"].ToString() != "")
                {
                    model.Date = DateTime.Parse(ds.Tables[0].Rows[0]["Date"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CID"].ToString() != "")
                {
                    model.CID = int.Parse(ds.Tables[0].Rows[0]["CID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["MID"].ToString() != "")
                {
                    model.MID = int.Parse(ds.Tables[0].Rows[0]["MID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Week"].ToString() != "")
                {
                    model.Week = int.Parse(ds.Tables[0].Rows[0]["Week"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Back"] != null)
                {
                    model.Back = ds.Tables[0].Rows[0]["Back"].ToString();
                }
                if (ds.Tables[0].Rows[0]["AVGWeight"] != null)
                {
                    model.AVGWeight = ds.Tables[0].Rows[0]["AVGWeight"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Weight"] != null)
                {
                    model.Weight = ds.Tables[0].Rows[0]["Weight"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Price"].ToString() != "")
                {
                    model.Price = decimal.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                }
                if (ds.Tables[0].Rows[0]["temp1"] != null)
                {
                    model.temp1 = ds.Tables[0].Rows[0]["temp1"].ToString();
                }
                if (ds.Tables[0].Rows[0]["temp2"] != null)
                {
                    model.temp2 = ds.Tables[0].Rows[0]["temp2"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsExamine"].ToString() != "")
                {
                    model.IsExamine = int.Parse(ds.Tables[0].Rows[0]["IsExamine"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ExamineUserID"].ToString() != "")
                {
                    model.ExamineUserID = int.Parse(ds.Tables[0].Rows[0]["ExamineUserID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ExamineUserName"] != null)
                {
                    model.ExamineUserName = ds.Tables[0].Rows[0]["ExamineUserName"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,FMID,Date,CID,MID,Week,Back,AVGWeight,Weight,Price,temp1,temp2,IsExamine,ExamineUserID,ExamineUserName ");
            strSql.Append(" FROM RecipeInformation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,FMID,Date,CID,MID,Week,Back,AVGWeight,Weight,Price,temp1,temp2,IsExamine,ExamineUserID,ExamineUserName ");
            strSql.Append(" FROM RecipeInformation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


        #endregion  Method


        #region 扩展方法

        /// <summary>
        /// 批量删除
        /// </summary>
        public bool DeleteListBywhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from RecipeInformation ");
            strSql.Append(" where " + where);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取数量
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public int GetCount(string strwhere)
        {
            int result = 0;
            string strWhere = "";
            if (strwhere.Length > 0)
            {
                strWhere += " where " + strwhere;
            }
            string sql = "SELECT COUNT(*) FROM RecipeInformation" + strwhere;
            object obj = DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                Int32.TryParse(obj.ToString(), out result);
            }
            return result;
        }

        /// <summary>
        /// 根据条件获取周食谱清单
        /// </summary>
        public DataSet GetWeekList(string ClassID, string FID, string Date)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.*,b.ClassID,c.name,c.IsStuffing as youxian from RecipeInformation as a inner join Recipes as b on a.MID=b.id inner join Dish as c on a.cid=c.id ");
            sb.Append("where b.ClassID=" + ClassID + " and a.FMID=" + FID + " and Date=convert(datetime,'" + Date + "')");
            return DbHelperSQL.Query(sb.ToString());

        }

        /// <summary>
        /// 获取视图View_RecipesInformation表内容
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns>AID：RecipeInformationInfo表ID</returns>
        /// <returns>RecipeInformationID：RecipeInformation表ID</returns>
        /// <returns>RawID：RawMaterial表ID</returns>
        /// <returns>Number：此原料的重量（以克为单位）</returns>
        /// <returns>FMID：FoodMealType表ID</returns>
        /// <returns>Date：食谱中配餐的时间</returns>
        /// <returns>CID：Dish表ID</returns>
        /// <returns>RecipesID：Recipes表ID</returns>
        /// <returns>dayofweek：一周中的第几天（周日为0）</returns>
        /// <returns>RAWName：RawMaterial表RawName</returns>
        /// <returns>DishName：Dish表Name</returns>
        /// <returns>WeekNo：第几周（从0开始，0代表第一周）</returns>
        /// <returns>DTID：DishType表ID（是主食还是小菜、汤类、粥等）</returns>
        /// <returns>RecipesWeekID:RecipesWeek表ID</returns>
        public DataSet GetViewList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [AID],[RecipeInformationID],[RawID],[Number],[CanBie],[FMID],[Date],[CID],[RecipesID],[dayofweek],[RAWName],[DishName],[WeekNo],[DTID],[RecipesWeekID],RAWZS ");
            strSql.Append(" FROM [View_RecipesInformation] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 检查 食谱
        /// </summary>
        /// <param name="classID">班组ID</param>
        /// <param name="FoodID">餐别ID 0表示所有餐别</param>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <param name="ExamineUserID">检查人用户ID</param>
        /// <param name="ExamineUserName">检查人用户名</param>
        /// <returns>是否检查成功</returns>
        public bool Examine(int classID, int FoodID, string startDate, string endDate, int ExamineUserID, string ExamineUserName)
        {
            StringBuilder strsql = new StringBuilder();
            string strwhere = " b.ClassID=" + classID + " and a.[Date]>=CONVERT(datetime,'" + startDate + "') and a.[date] <=CONVERT(datetime,'" + endDate + "')";
            if (FoodID != 0) //0表示所有餐别
            {
                strwhere += " and a.FMID=" + FoodID;
            }
            strsql.Append("update RecipeInformation set IsExamine=1,ExamineUserID=" + ExamineUserID + ",ExamineUserName='" + ExamineUserName + "'");
            strsql.Append("where id in ( select a.id from RecipeInformation as a inner join Recipes as b on a.MID=b.id where " + strwhere + ")");
            return DbHelperSQL.ExecuteSql(strsql.ToString()) > 0;
        }

        /// <summary>
        /// 获取检查数据
        /// </summary>
        /// <param name="classID">班组ID</param>
        /// <param name="FoodID">餐别ID</param>
        /// <param name="startDate">日期</param>
        /// <returns>返回所需要查询的餐别数据</returns>
        public DataTable GetExamineDt(int classID, int FoodID, string strDate)
        {
            StringBuilder strsql = new StringBuilder();
            string strwhere = " where a.FMID=" + FoodID + " and b.ClassID=" + classID + " and a.[Date]=CONVERT(datetime,'" + strDate + "')";
            strsql.Append("select a.*,c.IsStuffing as youxian from RecipeInformation as a inner join Recipes as b on a.MID=b.id  inner join Dish as c on a.cid=c.id ");
            strsql.Append(strwhere);
            return DbHelperSQL.Query(strsql.ToString()).Tables[0];
        }


        /// <summary>
        /// 判断该条件下的食谱是否存在检查
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public bool IsExistsExamineByWhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from RecipeInformation where IsExamine>0 ");
            if (!string.IsNullOrEmpty(where))
            {
                strSql.Append(" and " + where);
            }
            return int.Parse(DbHelperSQL.ExecuteScalar(strSql.ToString()).ToString()) > 0;
        }

        /// <summary>
        /// 获取食谱检查报表
        /// </summary>
        /// <param name="StartTime">日期开始时间</param>
        /// <param name="EndTime">日期结束时间</param>
        /// <param name="strWhere">食谱查询条件</param>
        /// <returns></returns>
        public DataTable GetExamineDtForMonitor(string StartTime, string EndTime, string strWhere)
        {
            StringBuilder strsql = new StringBuilder();
            if (strWhere.Length > 0)
            {
                strWhere = " where " + strWhere;
            }
            strsql.Append(@"select Date,IsExamineByDate,AreaID,ClassID from
                            (
	                            select A.Date,A.IsExamine,
	                            case when 
	                            (
		                            select COUNT(1) from RecipeInformation as T1 left join Recipes as T2 on T1.MID=T2.id 
		                            where T1.Date=A.Date and T2.ClassID=B.ClassID and (A.IsExamine is null or A.IsExamine=0)
	                            )>0 then 0 else 1 end as IsExamineByDate,
	                            B.AreaID,B.ClassID from 
	                            (
		                            select * from RecipeInformation where Date>=CAST('" + StartTime + @"' as datetime) and Date<cast('" + EndTime + @"' as datetime)
	                            ) as A join
	                            (
		                            select * from Recipes " + strWhere + @"
	                            ) as B on A.MID=B.id 
                            ) as T
                            group by Date,IsExamineByDate,AreaID,ClassID
                            order by Date asc");
            return DbHelperSQL.Query(strsql.ToString()).Tables[0];
        }
        #endregion
    }
}

